package cn.kgc.layui.dao.impl;

import cn.kgc.layui.dao.StudentDao;
import cn.kgc.layui.entity.Student;
import cn.kgc.layui.util.DruidDBUtil;
import com.alibaba.druid.util.StringUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * StudentDao的实现类
 * @author alex-ycp
 * @create 2021-07-16 14:52
 */
public class StudentDaoImpl implements StudentDao {
    @Override
    public List<Student> findAllByPage(int start, int size,String stuName) {

        // 获取QueryRunner
        QueryRunner qr = new QueryRunner(DruidDBUtil.getDatasource());
        //String sql = "select stu_no id , stu_name stuName,  sex , tel,class_no classNo ,age stuAge from student limit ?,?";
        String sql="select id,stu_no stuNo , stu_name stuName,  sex , tel,class_no classNo ,age stuAge from student";


        try {
            if(null!=stuName&&(!StringUtils.isEmpty(stuName))){
                sql+=" where stu_name=? ";
                sql+="  order by id desc limit ?,?";
                return qr.query(sql, new BeanListHandler<>(Student.class),stuName, start, size);
            }else{
                sql+=" order by id desc  limit ?,?";
                return qr.query(sql, new BeanListHandler<>(Student.class), start, size);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }


        return null;
    }

    @Override
    public int save(Student student) {
        int result=0;
        try {
            // 获取QueryRunner
            QueryRunner qr = new QueryRunner(DruidDBUtil.getDatasource());
            String sql="insert into student (stu_no,stu_name,tel) values(?,?,?)";
             result=qr.update(sql,student.getStuNo(),student.getStuName(),student.getTel());
            return result;
        }catch (Exception e){
            e.printStackTrace();
        }
        return result;

    }

    @Override
    public int update(Student student) throws SQLException {
        int result=0;
        try {
            // 获取QueryRunner
            QueryRunner qr = new QueryRunner(DruidDBUtil.getDatasource());
            String sql="update student set stu_no=?,stu_name=?,tel=? where id=?";
            result=qr.update(sql,student.getStuNo(),student.getStuName(),student.getTel(),student.getId());
            return result;
        }catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public int delete(String id) throws SQLException {
        int result=0;
        try {
            // 获取QueryRunner
            QueryRunner qr = new QueryRunner(DruidDBUtil.getDatasource());
            String sql="delete from student where id=?";
            result=qr.update(sql,id);
            return result;
        }catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public long selectTotalCount() {

        QueryRunner qr = new QueryRunner(DruidDBUtil.getDatasource());
        String sql = "select count(*) from student";
        try {
            return qr.query(sql,new ScalarHandler<Long>());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return 0;
    }
}
